/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.lcfgly.DatabaseFactory;

import com.lcfgly.Exception.SQLEmptyException;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

/**
 * 数据库工具基类
 * @author lcfgly
 */
class DataFactory implements DataFactoryInterface {
    
    private final JdbcTemplate jdbcTemplate;

    DataFactory(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate=jdbcTemplate;
    }
    
    @Override
    public <T> int  insert(T object,String table) throws SQLException
    {
        StringBuffer key=new StringBuffer();
        StringBuffer value=new StringBuffer();
        Object[] params=setParams(object, key, value);
        String sql="INSERT INTO "+table+" ( "+key.substring(0,key.length()-1)+" ) VALUE ( "+value.substring(0,value.length()-1)+" )";
        try{
            return jdbcTemplate.update(sql, params);
        }catch (DataAccessException e) {
            throw new SQLException(e.getMessage());
        }
    }
    
    @Override
    public <T> int update(String table,T object,String keyname,Object... key) throws SQLException
    {
        StringBuffer sql=new StringBuffer();
        Object[] params=setUpdateparam(object, sql,key.length);
        String sqle="UPDATE "+table+" SET "+sql.substring(0, sql.length()-1)+" WHERE "+keyname; 
        int x=params.length-key.length;
        for(int i=x;i<params.length;i++)
            params[i]=key[i-x];
        try{
            return jdbcTemplate.update(sqle, params);
        }catch (DataAccessException e) {
            throw new SQLException(e.getMessage());
        }
    }
    
    @Override
    public int delete(String table,String keyname,Object... key) throws SQLException
    {
        String sql="DELETE FROM "+table+" WHERE "+keyname;
        try{
            return jdbcTemplate.update(sql, key);
        }catch (DataAccessException e) {
            throw new SQLException(e.getMessage());
        }
    }
    
    @Override
    public int delete_list(String table,String keyname,Object... keys) throws SQLException
    {
        String x="";
        for (Object object : keys) {
            x+="?,";
        }
        x=x.substring(0, x.length()-1);
        String sql="DELETE FROM "+table+" WHERE "+keyname+" IN ( "+x+")";
        try{
            return jdbcTemplate.update(sql, keys);
        }catch (DataAccessException e) {
            throw new SQLException(e.getMessage());
        }
    }
    
    @Override
    public <T> int[] insert_list(List<T> args,String table) throws SQLException
    {
        StringBuffer key=new StringBuffer();
        StringBuffer value=new StringBuffer();
        List<Object[]> params=new ArrayList<Object[]>();
        for(T object:args)
        {
            key.setLength(0);
            value.setLength(0);
            params.add(setParams(object, key, value));
        }
        String sql="INSERT INTO "+table+" ( "+key.substring(0,key.length()-1)+" ) VALUE ( "+value.substring(0,value.length()-1)+" )";
        try{
           return  jdbcTemplate.batchUpdate(sql, params);
        }catch (DataAccessException e) {
            throw new SQLException(e.getMessage());
        }
    }
    
    @Override
    public <T> int insertBackID(T object,String table)throws  SQLException
    {
        StringBuffer key=new StringBuffer();
        StringBuffer value=new StringBuffer();
        KeyHolder keyHolder = new GeneratedKeyHolder();
        final Object[] params=setParams(object, key, value);
        final String sql="INSERT INTO "+table+" ( "+key.substring(0,key.length()-1)+" ) VALUE ( "+value.substring(0,value.length()-1)+" )";
        try {
            jdbcTemplate.update(new PreparedStatementCreator() {
                            @Override
                            public PreparedStatement createPreparedStatement(Connection con)throws SQLException {
                                            PreparedStatement ps = jdbcTemplate.getDataSource().getConnection().prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
                                            for(int i = 0;i<params.length;i++){
                                                    ps.setObject(i+1, params[i]);
                                            }
                                        return ps;
                            }
            }, keyHolder);
        } catch (DataAccessException e) {
            throw new SQLException(e.getMessage());
        }
        return keyHolder.getKey().intValue();
    }
    
    private <T> Object[] setParams(T object,StringBuffer key,StringBuffer value) throws SQLException
    {
        Class c=object.getClass();
        Field[] f=c.getDeclaredFields();
        ArrayList<Object> fs=new ArrayList<Object>();
        for(Field f1 : f)
        {
            String paramname=f1.getName();
            if(!paramname.equals("id") && !paramname.equals("serialVersionUID"))
            {
                f1.setAccessible(true);
                try {
                    Object param=f1.get(object);
                    key.append(paramname);
                    key.append(",");
                    value.append("?,");
                    fs.add(param);
                } catch (IllegalArgumentException ex) {
                     throw new SQLException("类型转换失败！", ex);
                } catch (IllegalAccessException ex) {
                     throw new SQLException("类型转换失败！", ex);
                }
            }
        }
        Object[] params=new Object[fs.size()];
        int i=0;
        for (Object object1 : fs) {
            params[i++]=object1;
        }
        return params;
    }

    private <T> Object[] setUpdateparam(T object,StringBuffer sql,int keylength) throws SQLException
    {
        Class c=object.getClass();
        Field[] f=c.getDeclaredFields();
        ArrayList<Object> fs=new ArrayList<Object>();
        for(Field f1 : f)
        {
            String paramname=f1.getName();
            if(!paramname.equals("id") && !paramname.equals("serialVersionUID"))
            {
                f1.setAccessible(true);
                try {
                    Object param=f1.get(object);
                    if(param != null){
                        sql.append( paramname);
                        sql.append("=?,");
                        fs.add(param);
                    }
                } catch (IllegalArgumentException ex) {
                    throw new SQLException("类型转换失败！", ex);
                } catch (IllegalAccessException ex) {
                     throw new SQLException("类型转换失败！", ex);
                }
            }
        }
        Object[] params=new Object[fs.size()+keylength];
        int i=0;
        for (Object object1 : fs) {
            params[i++]=object1;
        }
        return params;
    }
    
    @Override
    public <T> T one(Class<T> classtype,String sql,Object... args) throws SQLException
    {
        try{
             return (T)jdbcTemplate.queryForObject(sql,args, new BeanPropertyRowMapper(classtype));
        }catch(EmptyResultDataAccessException e){
            throw new SQLEmptyException(e);
        }catch(DataAccessException e){
            throw new SQLException(e.getMessage());
        }
    }
    
    @Override
    public <T> List<T> rows(Class<T> classtype,String sql,Object... args) throws SQLException
    {
        try{
            return jdbcTemplate.query(sql, args, new BeanPropertyRowMapper(classtype));
        }catch(EmptyResultDataAccessException e){
            throw new SQLEmptyException(e);
        }catch(DataAccessException e){
            throw new SQLException(e.getMessage());
        }
    }
    
    @Override
    public List rowsWithOutObject(String sql,Object... args) throws SQLException
    {
        try{
            return jdbcTemplate.queryForList(sql, args);
        }catch(EmptyResultDataAccessException e){
            throw new SQLEmptyException(e);
        }catch(DataAccessException e){
            throw new SQLException(e.getMessage());
        }
    }
    
    @Override
    public Map oneWithOutObject(String sql,Object... args) throws SQLException
    {
        try{
            return jdbcTemplate.queryForMap(sql, args);
        }catch(EmptyResultDataAccessException e){
            throw new SQLEmptyException(e);
        }catch(DataAccessException e){
            throw new SQLException(e.getMessage());
        }
    }
     
    @Override
     public int Getrowsnum(String sql,Object...args) throws SQLException
     {
         try{
             return jdbcTemplate.queryForObject(sql,args, Integer.class);
         }catch(EmptyResultDataAccessException e){
             return 0;
        }catch(DataAccessException e){
            throw new SQLException(e.getMessage());
        }
     }
     
     @Override
     public Map Page(Class classtype,String sql,int rowsnum,int page,int pagecount,Object...args)throws SQLException{
         Map result=new HashMap();
         if(rowsnum == 0){
             result.put("page", paging(1, 1));
             result.put("data", null);
         }else{
            int pagenum=rowsnum/pagecount;
            //page分页码
            result.put("page", paging(pagenum+1, page));
            page=(page-=1)<0?0:page;
            page=page>pagenum?pagenum:page;
            String s=sql+" limit "+page*pagecount+","+pagecount;
            if(classtype==null)
                result.put("data", rowsWithOutObject(s, args));
            else
                result.put("data", rows(classtype, s, args));
         }
         return result;
     }
     
     /**
      * 页码
      * @param allpage
      * @param nowpage
      * @return 
      */
     private Map paging(int allpage,int nowpage){
         Map result=new HashMap();
         result.put("lastpage", allpage);
         result.put("firstpage", 1);
         result.put("nowpage", nowpage);
         //当前分页中第一页的页码
         int i=(nowpage-2)>1?nowpage-2:1;
         int top=(i+5)>allpage?allpage:i+5;
         int[] x=new int[top-i+1];
         for(int j=0;i<=top;i++,j++){
             x[j]=i;
         }
         result.put("page", x);
         int uppage=nowpage>1?nowpage-1:1;
         int downpage=nowpage+1>allpage?allpage:nowpage+1;
         result.put("uppage", uppage);
         result.put("downpage", downpage);
         return result;
     }
     
}
